<?php

class ModelOrder
{

    public function construct()
    {

    }

   

    public function getOrderByID($orderid)
    {
        $sql = "SELECT * from tbl_orders o inner join tbl_user u on o.customer_id=u.user_Id where o.order_id=" . $orderid . " ";

        $item_arr = DB::fetchAll($sql);

        return $item_arr;
    }

     public function cancelorder($request)
    {
        $sql = "update tbl_orders set iscancelled=1 where  order_id=" . $request["id"] . " ";
        $cancelled = DB::exec($sql);
        if($cancelled){

            echo "<span class='success'>Order is Cancelled!</span>";
        }
          
      
    }

    public function gettotalrowcount_not_processed()
    {


        $sql = "SELECT count(*) as count from tbl_orders  where iscancelled=0 and ispaid=0 and isDelivered=0   order by order_date desc  ";

        $count = DB::fetchRow($sql);

        return $count["count"];
    }
        public function listOrders_not_processed($limit)
    {
        $sql = "SELECT * from tbl_orders o inner join tbl_user u on o.customer_id=u.user_Id  where iscancelled=0 and ispaid=0 and isDelivered=0   order by order_date desc ";

        $sql .= $limit;
        //echo $sql;
        $item_arr = DB::fetchAll($sql);

        return $item_arr;
    }

      public function gettotalrowcount_delivered_notpaid()
    {


         $sql = "SELECT count(*) as count from tbl_orders  where iscancelled=0 and ispaid=0 and isDelivered=1   order by order_date desc  ";

        $count = DB::fetchRow($sql);

        return $count["count"];
    }
        public function listOrders_delivered_notpaid($limit)
    {
        $sql = "SELECT * from tbl_orders o inner join tbl_user u on o.customer_id=u.user_Id  where iscancelled=0 and ispaid=0 and isDelivered=1   order by order_date desc ";

        $sql .= $limit;
        // echo $sql;
        $item_arr = DB::fetchAll($sql);
  //print_r($item_arr);
        return $item_arr;
    }
  public function gettotalrowcount_paid_notdelivered()
    {


        $sql = "SELECT count(*) as count from tbl_orders  where iscancelled=0 and ispaid=1 and isDelivered=0  order by order_date desc  ";

        $count = DB::fetchRow($sql);

        return $count["count"];
    }
        public function listOrders_paid_notdelivered($limit)
    {
        $sql = "SELECT * from tbl_orders o inner join tbl_user u on o.customer_id=u.user_Id  where iscancelled=0 and ispaid=1 and isDelivered=0   order by order_date desc ";

        $sql .= $limit;
        // echo $sql;
        $item_arr = DB::fetchAll($sql);

        return $item_arr;
    }

      public function gettotalrowcount_cancelled()
    {


        $sql = "SELECT count(*) as count from tbl_orders  where iscancelled=1  order by order_date desc  ";

        $count = DB::fetchRow($sql);

        return $count["count"];
    }
        public function listOrders_cancelled($limit)
    {
        $sql = "SELECT * from tbl_orders o inner join tbl_user u on o.customer_id=u.user_Id  where iscancelled=1  order by order_date desc ";

        $sql .= $limit;
        // echo $sql;
        $item_arr = DB::fetchAll($sql);

        return $item_arr;
    }

      public function gettotalrowcount_completed()
    {


        $sql = "SELECT count(*) as count from tbl_orders  where iscancelled=0 and ispaid=1 and isDelivered=1   order by order_date desc  ";

        $count = DB::fetchRow($sql);

        return $count["count"];
    }
        public function listOrders_completed($limit)
    {
        $sql = "SELECT * from tbl_orders o inner join tbl_user u on o.customer_id=u.user_Id  where iscancelled=0 and ispaid=1 and isDelivered=1  order by order_date desc ";

        $sql .= $limit;
        // echo $sql;
        $item_arr = DB::fetchAll($sql);

        return $item_arr;
    }

    public function getCart($cartid)
    {

        $sql = "SELECT p.productid,p.item_code,s.price,p.product_title,s.quantity from  tbl_shoppingcart s inner join tbl_products p on p.productid=s.product_id  where cart_id=" . $cartid . "";
        //  echo $sql;exit;
        $cart = DB::fetchAll($sql);

        return $cart;
    }

    public function processorder($formVal)
    {

        $orderid = $formVal['orderid'];
        $ordertotal = $formVal['ordertotal'];
        $ispaid = $formVal['ispaid'];
        $isdelivered = $formVal['isdelivered'];
        $userid= $formVal['userid'];
        $updatecredit=0;
        if(empty($isdelivered)){
            $isdelivered=0;
        }
        $orderdetails=$this->getorderdetailsforcredit($orderid);
       // print_r($orderdetails);
        $sql = "UPDATE tbl_orders SET ispaid = " . $ispaid . ",isdelivered=" . $isdelivered . "  WHERE order_id = " . $orderid . " ";
        //  echo $sql;
        $order_arr = DB::exec($sql);
       // echo mysql_affected_rows()."".$this->iscredituser($userid)."".$this->creditbalance($userid);
        if(mysql_affected_rows ()>0 && $this->iscredituser($userid) && $this->creditbalance($userid)>0 ){
            
           // $ordertotal=$this->getordertotal($orderid);
            $isvalidupdate=0;
            if($orderdetails["ispaid"]==0){
               if($orderdetails["isdelivered"]==0){
                    if($isdelivered==1 && $ispaid==0  ){
                        $transtype=3;
                        $isvalidupdate=1;
                    }
                    elseif($isdelivered==0 && $ispaid==1 ){
                        //$transtype=3;
                        $isvalidupdate=0;
                    }
                    elseif($isdelivered==1 && $ispaid==1 ){
                         $isvalidupdate=0;
                    }
                     
               }
               else{
                    if($isdelivered==1 && $ispaid==0  ){
                        $isvalidupdate=0;
                    }
                    elseif($isdelivered==0 && $ispaid==1 ){
                        //$transtype=3;
                        $isvalidupdate=0;
                    }
                    elseif($isdelivered==1 && $ispaid==1 ){
                        $transtype=4;
                        $isvalidupdate=1;
                    }
                    elseif($isdelivered==0 && $ispaid==0 ){
                         $transtype=8;
                         $isvalidupdate=1;
                    }
                   
               }
            }
            else{
                if($orderdetails["isdelivered"]==0){
                    if($isdelivered==1 && $ispaid==0  ){
                        $transtype=5;
                        $isvalidupdate=1;
                    }
                    elseif($isdelivered==0 && $ispaid==1 ){
                        //$transtype=3;
                        $isvalidupdate=0;
                    }
                    elseif($isdelivered==1 && $ispaid==1 ){
                         $isvalidupdate=0;
                    }
               }
               else{
                    if($isdelivered==1 && $ispaid==0  ){
                        $isvalidupdate=1;
                        $transtype=6;
                    }
                    elseif($isdelivered==0 && $ispaid==1 ){
                        $isvalidupdate=0;
                    }
                    elseif($isdelivered==1 && $ispaid==1 ){
                          $isvalidupdate=0;
                    }
                  }
            }
           // print_r($orderdetails);
            if($isvalidupdate==1){
                $sqlcreditupdate=" insert into tbl_creditaccount (userid,transtype,transvalue,transdate,orderid) values  (".$userid.",".$transtype.",".$ordertotal.",now(),".$orderid.") ";
                //echo $sqlcreditupdate;
                DB::exec($sqlcreditupdate);
            }
            
        }
        if ($order_arr)
        {
            echo "<span class='success'>Order details has been successfully updated</span>";
        }
    }

    public function iscredituser($userid){
        $sql = "SELECT * from  tbl_user  where user_id =".$userid."  and iscreditor=1 ";
       // echo $sql;
        $userarr = DB::fetchRow($sql);

        if($userarr){
            return true;
        }else{
            return false;
        }
    }

       public function creditbalance($userid){
        $accountbalance=0;
        $sql = "SELECT  *  from  tbl_creditaccount  where userid =".$userid." ";
        $credit= DB::fetchAll($sql);
        foreach($credit as $arr){
         if(in_array($arr["transtype"], array(0,1,4,8))){
            $accountbalance+=(float)$arr["transvalue"];
         }
         else{
            $accountbalance-=(float)$arr["transvalue"];

         }
        }
        return $accountbalance;
    }

    public function getorderdetailsforcredit($orderid){
        $sql = "SELECT *  from  tbl_orders  where order_id =".$orderid . "";
        //echo $sql;
        $orderdetails = DB::fetchRow($sql);
        // print_r($orderdetails);
        if($orderdetails){
            return $orderdetails;
        } 
    }

     public function getordertotal($orderid){
        $sql = "SELECT *  from  tbl_shoppingcart  where order_id =".$orderid . "";
        echo $sql;
        $total=0;
        $orderdetails = DB::fetchAll($sql);
        foreach($orderdetails as $details){
            $total+=(int)$details["price"]*(int)$details["quantity"];
        }
        // print_r($orderdetails);
        return $total;
    }

    public function saveProduct($formVal)
    {
        // print_r($formVal);
        $item_code = $formVal['item_code'];
        $product_title = $formVal['product_title'];
        $productdesc_en = $formVal['product_desc_en'];
        $productdesc_detail = $formVal['product_desc_detail'];
        $product_price_lkr = $formVal['product_price_lkr'];
        $product_qty = $formVal['product_qty'];
        $category = $formVal['category'];
        $subcategory = !empty($formVal['subcat']) ? $formVal['subcat'] : 0;
        $status = $formVal['status'];

        $sql = "insert into tbl_products (item_code,product_title,product_dsc_en,product_dsc_detail
                    ,product_price_lkr,quantity,product_cat_id,product_subcat_id,created_date,status) values
                     ('" . $item_code . "','" . $product_title . "','" . $productdesc_en . "','" . $productdesc_detail . "'," . $product_price_lkr . "," . $product_qty . "," . $category . "," . $subcategory . ",now()," . $status . ")";

        //echo $sql;

        $product_arr = DB::exec($sql);

        if ($product_arr)
        {
            echo "<span class='success'>Product details has been successfully updated</span>";
        }
    }

    public function getProductByItemCode($productid)
    {
        $sql = "SELECT P.productid,P.item_code,P.product_title, P.product_dsc_en, P.product_dsc_detail, P.product_dsc_tamil, P.product_price_lkr, P.quantity, P.product_cat_id, P.product_subcat_id, P.created_date, P.status, C.cat_desc
				FROM tbl_products P
				LEFT JOIN tbl_category C ON C.cat_id = P.product_subcat_id
				WHERE P.productid =" . $productid . " ";
        //echo $sql;
        $item_arr = DB::fetchAll($sql);

        return $item_arr;
    }

    public function getFeaturesbyProductId($productid)
    {

        $sql = "SELECT * from  tbl_productfeatures  where productid =" . $productid . " ";
        //echo $sql;
        $item_arr = DB::fetchAll($sql);

        return $item_arr;
    }

    public function saveFeaturesbyProductId($request)
    {
        //print_r($request);
        $dberror = 0;
        $insertstructarray = array();
        foreach ($request["structkey"] as $key => $val)
        {
            if (strlen($request["structkeyopt"][$key]) > 0)
            {
                $insertstructarray[$request["structkeyopt"][$key]] = $request["structval"][$key];
            } else
            {
                $insertstructarray[$val] = $request["structval"][$key];
            }
        }
        //print_r($insertstructarray);
        DB::exec("SET AUTOCOMMIT=0");
        DB::exec("START TRANSACTION");
        $sqldelete = DB::exec("delete from  tbl_productfeatures  where productid =" . $request["productid"] . "");
        foreach ($insertstructarray as $structurekey => $structurevalue)
        {
            $sqlinsert = DB::exec("insert  into  tbl_productfeatures (structkey,structvalue,productid) values ('" . $structurekey . "','" . $structurevalue . "'," . $request["productid"] . ")");
            if (mysql_error ())
            {
                $dberror = 1;
            }
            $keyexistintemptablesql = "select * from tbl_tempstructuretable where structkey='" . $structurekey . "' and catid=" . $request["catid"] . "";
            $keyexistintemptable = DB::fetchAll($keyexistintemptablesql);
            if (!$keyexistintemptable)
            {
                $inserttemptablesql = "insert into  tbl_tempstructuretable (catid,structkey) values (" . $request["catid"] . ",'" . $structurekey . "')";
                $inserttemptablesql;
                $inserttemptable = DB::exec($inserttemptablesql);
                if (mysql_error ())
                {
                    $dberror = 1;
                }
            }
        }
        if ($dberror == 0)
        {
            DB::exec("COMMIT");
            echo "<span class='success'>Product details has been successfully updated</span>";
        } else
        {
            DB::exec("ROLLBACK");
            echo "<span class='error'>Update failed.Database error occured!</span>";
        }


        // exit;
        return $success;
    }

    function getcatidfromproductid($productid)
    {
        $sql = "select product_cat_id as catid from tbl_products where productid=" . $productid . "";
        $catid = DB::fetchRow($sql);
        return $catid["catid"];
    }

    function listStructfields($catid)
    {
        $sql = "select *   from tbl_tempstructuretable where catid=" . $catid . "";
        $structarr = DB::fetchAll($sql);
        return $structarr;
    }

    function deleteFeaturesbyProductId($formVal)
    {
        $productid = $formVal['productid'];
        $structid = $formVal['structid'];
        $sql = "delete from tbl_productfeatures where structid=" . $structid . " and productid=" . $productid . "";
        // echo $sql;
        $delete = DB::exec($sql);
        if ($delete)
        {
            echo "<span class='success'>Fetaure delete successfully!</span>";
        }
    }

    public function getProductImages($productid)
    {
        DB::exec("SET NAMES 'utf8'");

        $sql = "SELECT *
				FROM tbl_productimage
				WHERE productid = " . $productid . " ";
        //echo $sql;
        $item_arr = DB::fetchAll($sql);

        return $item_arr;
    }

    public function addProductImage($productid, $filename)
    {
        $sql1 = "SELECT COUNT(image_id) AS IMGCOUNT
				 FROM tbl_productimage
				 WHERE filename = '" . $filename . "' ";

        $rs = DB::fetchRow($sql1);
        $rw_count = $rs['IMGCOUNT'];
        if ($rw_count == 0)
        {
            $sql2 = "INSERT INTO tbl_productimage(productid, filename)
					VALUES('" . $productid . "', '" . $filename . "')";
            $add_productimage = DB::exec($sql2);
            if ($add_productimage)
            {
                echo "success";
            }
        } else
        {
            echo "error";
        }
    }

    public function delProductImage($productid, $image_id)
    {
        $sql1 = "SELECT filename
				 FROM tbl_productimage
				 WHERE productid = " . $productid . " and image_id=" . $image_id . "";

        $rs = DB::fetchRow($sql1);

        $del_thumb = unlink("../images/products/thumbs/" . $rs['filename']);
        $del_large = unlink("../images/products/" . $rs['filename']);
        if ($del_large)
        {
            $sql2 = "DELETE FROM tbl_productimage
					 WHERE productid = " . $productid . " and image_id=" . $image_id . "";
            //echo $sql2;
            $del_img = DB::exec($sql2);
            if ($del_img)
            {
                echo "Selected product images has been successfully deleted";
            }
        }
    }

}

?>